/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ssi.mrms.dao.Impl;

import static java.text.MessageFormat.format;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.ssi.mrms.dao.RequestDao;
import com.ssi.mrms.dao.mapping.RequestMapping;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.RMsreqhdr;
import com.ssi.mrms.model.TowerModel;
import com.ssi.mrms.utils.DateUtils;
import com.ssi.mrms.utils.SecurityUtils;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class RequestDaoImpl extends AbstractBaseDao implements RequestDao {

    @Autowired JdbcTemplate jdbcTemplate;

    private static final String SQL_QUERY = " SELECT * FROM rmsreqhdr ";
    private static final String SQL_QUERY_BY_ID = " SELECT A.* , TW.* FROM rmsreqhdr A  LEFT JOIN RMRMLST TW ON TW.RLRMID =  A.RLRMID  WHERE A.SREQHID = ?";
    private RMsreqhdr model;
    @Override
    public List<RMsreqhdr> findAll() {

        List<RMsreqhdr> list = jdbcTemplate.query(SQL_QUERY, new Object[]{}, new RequestMapping());

        return list == null ? new ArrayList<RMsreqhdr>() : list;

    }

    @Deprecated
    @Override
    public List<Map<String, Object>> findAllMap() {

        return jdbcTemplate.queryForList(SQL_QUERY);

    }

    @Override
    public RMsreqhdr findById(Integer sreqhid) {
    	
        List<RMsreqhdr> model = jdbcTemplate.query(SQL_QUERY_BY_ID, new Object[]{sreqhid}, new RequestMapping());

        return model == null ? new RMsreqhdr() : model.get(0);

    }

    private static final String SQL_INSERT_rmsreqhdr = " INSERT INTO rmsreqhdr(SREQHDOCNO,SREQHSTAT,SREQHPRT,SREQHREQBY,SREQHREQDAT,SREQHTEL, SREQHDESC, SREQHLHT, SREQHTELELE, SREQHAIRCON, SREQHSTR, SREQHAPL, SREQHPOOL, SREQHCLN, SREQHSCR, SREQHCONNM, SREQHASTO, SREQHRESDAT, SREQHRESTM, SREQHCUS, SREQHSPTM, SREQHWTTM, SREQHCUSWTTM, RLRMID, SREQHCREBY, SREQHCREDAT, SREQHUPDBY, SREQHUPDDAT, SREQHWTDAT, SREQCPTDATE, SREQCPTTM)VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURDATE(), ?, CURDATE(),  ?, ?, ?)";

    @Override
    public void create(final RMsreqhdr model) throws Exception {
    	
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_rmsreqhdr), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                
//                ps.setInt(++i, model.getSreqhid());
                ps.setString(++i, model.getSreqhdocno());
                ps.setString(++i, model.getSreqhstat());
                ps.setString(++i, model.getSreqhprt());
                ps.setString(++i, model.getSreqhreqby());
                ps.setDate(++i, model.getSreqhreqdat() == null ? null : new java.sql.Date(model.getSreqhreqdat().getTime()));
                ps.setString(++i, model.getSreqhtel());
                ps.setString(++i, model.getSreqhdesc());
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhlht())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhtelele())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhaircon())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhstr())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhapl())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhpool())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhcln())? "N":"Y");
                ps.setString(++i, StringUtils.isEmpty(model.getSreqhscr())? "N":"Y");
                ps.setString(++i, model.getSreqhconnm());
                ps.setString(++i, model.getSreqhasto());
                ps.setDate(++i, model.getSreqhresdat() == null ? null : new java.sql.Date(model.getSreqhresdat().getTime()));
                ps.setString(++i, "".equals(model.getSreqhrestm())?null : model.getSreqhrestm());
                ps.setString(++i, model.getSreqhcus());
                ps.setString(++i, "".equals(model.getSreqhsptm())?null : model.getSreqhsptm());
                ps.setString(++i, "".equals(model.getSreqhwttm())?null : model.getSreqhwttm());
                ps.setString(++i, model.getSreqhcuswttm());
                ps.setInt(++i, model.getRlrmid());
                ps.setString(++i, SecurityUtils.getUsername());
                ps.setString(++i, SecurityUtils.getUsername());
//                ps.setDate(++i,new Date(DateUtils.getDateFormate(model.getSreqhspdat()).getTime()));
                ps.setDate(++i, model.getSreqhwtdat() == null ? null : new java.sql.Date(model.getSreqhwtdat().getTime()));
                ps.setDate(++i, model.getSreqcptdate() == null ? null : new java.sql.Date(model.getSreqcptdate().getTime()));
                ps.setString(++i, "".equals(model.getSreqcpttm())?null : model.getSreqcpttm());
                System.out.println("insert sql = "+ps);
                return ps;
            }

        }, keyHolder);
    }

    private static final String SQL_UPDATE_rmsreqhdr = "UPDATE rmsreqhdr  SET SREQHDOCNO = ? ,  SREQHSTAT = ? ,  SREQHPRT = ? ,  SREQHREQBY = ? ,  SREQHREQDAT = ? ,  SREQHTEL = ? ,  SREQHDESC = ? ,  SREQHLHT = ? ,  SREQHTELELE = ? ,  SREQHAIRCON = ? ,  SREQHSTR = ? ,  SREQHAPL = ? ,  SREQHPOOL = ? ,  SREQHCLN = ? ,  SREQHSCR = ? ,  SREQHCONNM = ? ,  SREQHASTO = ? ,  SREQHRESDAT = ? ,  SREQHRESTM = ? ,  SREQHCUS = ? ,  SREQHSPTM = ? ,  SREQHWTTM = ? ,  SREQHCUSWTTM = ? ,  RLRMID = ? ,  SREQHUPDBY = ? ,  SREQHUPDDAT = ? ,  SREQHSPDAT = ? ,  SREQHWTDAT = ? ,  SREQCPTDATE = ? ,  SREQCPTTM = ?  WHERE SREQHID = ?";

    @Override
    public boolean update(RMsreqhdr model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_rmsreqhdr),
                new Object[]{
                    model.getSreqhdocno(),
                    model.getSreqhstat(),
                    model.getSreqhprt(),
                    model.getSreqhreqby(),
                    model.getSreqhreqdat(),
                    model.getSreqhtel(),
                    model.getSreqhdesc(),
                    model.getSreqhlht(),
                    model.getSreqhtelele(),
                    model.getSreqhaircon(),
                    model.getSreqhstr(),
                    model.getSreqhapl(),
                    model.getSreqhpool(),
                    model.getSreqhcln(),
                    model.getSreqhscr(),
                    model.getSreqhconnm(),
                    model.getSreqhasto(),
                    model.getSreqhresdat(),
                    model.getSreqhrestm(),
                    model.getSreqhcus(),
                    model.getSreqhsptm(),
                    model.getSreqhwttm(),
                    model.getSreqhcuswttm(),
                    model.getRlrmid(),
                    SecurityUtils.getUsername(),
                    model.getSreqhspdat(),
                    model.getSreqhwtdat(),
                    model.getSreqcptdate(),
                    model.getSreqcpttm(),
                    model.getSreqhid()
                   
                });
        return row > 0;
    }

    private static final String SQL_DELETE_rmsreqhdr = "DELETE FROM rmsreqhdr  WHERE SREQHID = ?";

    @Override
    public boolean delete(RMsreqhdr model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_rmsreqhdr), new Object[]{model.getSreqhid()});
        return row > 0;
    }

	
	@SuppressWarnings("deprecation")
	@Override
	public DataTableModel<RMsreqhdr> findDataTable(RMsreqhdr model,	int page, int perPage) {
		
    	DataTableModel<RMsreqhdr> dataTableModel = new DataTableModel<RMsreqhdr>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{page, perPage});
        
        TowerModel tower = model.getTowerModel();

        /* --- Where Condition  ---*/
        String sql_where = ""; 
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(tower.getTower())) {
            sql_where = sql_where.concat(" AND b.RLSTTOWER = " + tower.getTower() + " ");
        }
        if (StringUtils.isNotBlank(tower.getFloor())) {
        	sql_where = sql_where.concat(" AND b.RLSTFLOOR = " + tower.getFloor() + " ");
        }
        if (StringUtils.isNotBlank(tower.getUnitNo())) {
        	sql_where = sql_where.concat(" AND b.RLSTUNITNO = " + tower.getUnitNo() + " ");
        }
        if (StringUtils.isNotBlank(tower.getAddress())) {
        	sql_where = sql_where.concat(" AND b.RLSTADDR = " + tower.getAddress() + " ");
        }
        
        /*--- Other Condition ----*/
        if (StringUtils.isNotBlank(model.getSreqhdocno())) {
        	sql_where = sql_where.concat(" AND a.SREQHDOCNO LIKE %" + model.getSreqhdocno() + "% ");
        }
        if (StringUtils.isNotBlank(model.getSreqhstat())) {
        	sql_where = sql_where.concat(" AND a.SREQHSTAT = " + model.getSreqhstat() + " ");
        }
        if (StringUtils.isNotBlank(model.getSreqhprt())) {
        	sql_where = sql_where.concat(" AND a.SREQHPRT = " + model.getSreqhprt() + " ");
        }
        
         /*--- Initial Query ---*/
		String sqlQuery = "SELECT * FROM rmsreqhdr a LEFT JOIN rmrmlst b ON a.RLRMID = b.RLRMID WHERE 1 = 1 "+sql_where+ sqlLimit;
		String sqlCount = "SELECT count(1) FROM rmsreqhdr a LEFT JOIN rmrmlst b ON a.RLRMID = b.RLRMID WHERE 1 = 1 "+sql_where;
		
		// --- #1 Get data for datatable by limit row
		List<RMsreqhdr> list = jdbcTemplate.query(sqlQuery, new RequestMapping());
		
		// --- #2 Get row number
		int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(page);
        dataTableModel.setLength(perPage);
        
        System.out.println("SQL : " + sqlQuery);
		return dataTableModel;
	}

}
